import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.*;

public class InsertImages {
    // JDBC 连接参数，请根据实际情况修改
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/votes"
            + "?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai"
            + "&useSSL=false&allowPublicKeyRetrieval=true&autoReconnect=true";
    private static final String JDBC_USER = "root";
    private static final String JDBC_PASS = "your_password_for_mysql";

    // 要扫描的目录
    private static final String IMAGE_DIR = "/home/Characters";
    // 创建者名称
    private static final String CREATOR_NAME = "SJJ";

    public static void main(String[] args) {
        System.out.println("开始插入图片数据...");
        
        // 检查目录是否存在
        File dir = new File(IMAGE_DIR);
        if (!dir.exists()) {
            System.err.println("图片目录不存在: " + dir.getAbsolutePath());
            System.out.println("请确认目录路径是否正确");
            return;
        }
        
        System.out.println("图片目录: " + dir.getAbsolutePath());

        // 扫描并上传
        try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS)) {
            System.out.println("数据库连接成功");
            
            // 检查表是否存在，如果不存在则创建
            createTableIfNotExists(conn);
            
            conn.setAutoCommit(false);

            String sql = "INSERT INTO images (image_data, image_name, creator, vote_count) VALUES (?, ?, ?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                File[] files = dir.listFiles((d, name) -> {
                    String lower = name.toLowerCase();
                    return lower.endsWith(".jpg")
                            || lower.endsWith(".jpeg")
                            || lower.endsWith(".png")
                            || lower.endsWith(".gif")
                            || lower.endsWith(".bmp")
                            || lower.endsWith(".webp");
                });

                if (files == null || files.length == 0) {
                    System.err.println("目录中没有找到图片文件: " + IMAGE_DIR);
                    System.out.println("支持的格式: jpg, jpeg, png, gif, bmp, webp");
                    return;
                }

                System.out.println("找到 " + files.length + " 个图片文件");
                int successCount = 0;
                int errorCount = 0;
                int skipCount = 0;

                for (File file : files) {
                    String filename = file.getName();
                    // 去掉文件扩展名
                    String nameWithoutExt = filename.replaceFirst("\\.[^.]+$", "");

                    byte[] data;
                    try {
                        data = Files.readAllBytes(Path.of(file.getAbsolutePath()));
                        System.out.println("正在处理: " + filename + " -> " + nameWithoutExt + " (大小: " + formatFileSize(data.length) + ")");
                    } catch (IOException ioe) {
                        System.err.println("读取文件失败: " + file.getAbsolutePath());
                        ioe.printStackTrace();
                        errorCount++;
                        continue;
                    }

                    // 检查图片是否已存在
                    if (imageExists(conn, nameWithoutExt)) {
                        System.out.println("图片已存在，跳过: " + nameWithoutExt);
                        skipCount++;
                        continue;
                    }

                    ps.setBytes(1, data);           // image_data
                    ps.setString(2, nameWithoutExt); // image_name
                    ps.setString(3, CREATOR_NAME);   // creator
                    ps.setInt(4, 0);                 // vote_count
                    ps.addBatch();
                    successCount++;
                }

                if (successCount > 0) {
                    int[] results = ps.executeBatch();
                    conn.commit();
                    System.out.println("✓ 成功插入 " + successCount + " 条记录");
                } else {
                    System.out.println("没有新的图片需要插入");
                }
                
                // 统计信息
                System.out.println("=== 处理完成 ===");
                System.out.println("成功插入: " + successCount + " 个");
                System.out.println("跳过重复: " + skipCount + " 个");
                System.out.println("处理失败: " + errorCount + " 个");
                System.out.println("总计文件: " + files.length + " 个");
                
            } catch (SQLException e) {
                try {
                    conn.rollback();
                    System.err.println("批量插入失败，已回滚");
                } catch (SQLException rollbackEx) {
                    System.err.println("回滚失败");
                    rollbackEx.printStackTrace();
                }
                e.printStackTrace();
            }
        } catch (SQLException e) {
            System.err.println("无法连接到数据库，请检查:");
            System.err.println("1. MySQL服务是否启动");
            System.err.println("2. 数据库 'votes' 是否存在");
            System.err.println("3. 用户名和密码是否正确");
            System.err.println("4. 网络连接是否正常");
            e.printStackTrace();
        }
        
        System.out.println("程序执行完成");
    }

    /**
     * 检查表是否存在，如果不存在则创建
     */
    private static void createTableIfNotExists(Connection conn) throws SQLException {
        String createTableSQL = """
            CREATE TABLE IF NOT EXISTS images (
                id INT AUTO_INCREMENT PRIMARY KEY,
                image_data LONGBLOB NOT NULL,
                image_name VARCHAR(255) NOT NULL UNIQUE,
                creator VARCHAR(100) NOT NULL DEFAULT 'SJJ',
                vote_count INT DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_creator (creator),
                INDEX idx_vote_count (vote_count)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """;
        
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(createTableSQL);
            System.out.println("表检查/创建完成");
        }
    }

    /**
     * 检查图片是否已存在
     */
    private static boolean imageExists(Connection conn, String imageName) throws SQLException {
        String sql = "SELECT COUNT(*) FROM images WHERE image_name = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, imageName);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() && rs.getInt(1) > 0;
            }
        }
    }

    /**
     * 格式化文件大小
     */
    private static String formatFileSize(long size) {
        if (size < 1024) return size + " B";
        if (size < 1024 * 1024) return String.format("%.1f KB", size / 1024.0);
        if (size < 1024 * 1024 * 1024) return String.format("%.1f MB", size / (1024.0 * 1024.0));
        return String.format("%.1f GB", size / (1024.0 * 1024.0 * 1024.0));
    }
}